================================================================================================================
                 ORACLE APEX EXAM – COMPLETE MASTER TEMPLATE (SQL, PL/SQL, REGEX, CHARTS, PAGES)
================================================================================================================

This file contains EVERYTHING required for APEX exam applications:
✔ Full SQL schema  
✔ PL/SQL trigger  
✔ Window functions  
✔ Model Clause  
✔ Regex validations  
✔ Authentication & Authorization  
✔ Step-by-step page creation  
✔ 10-page professional application structure  
✔ Fully general so you can modify for ANY domain (toys, school, hospital, banking…)

----------------------------------------------------------------------------------------------------------------
1. DATABASE SCHEMA (RUN IN ORACLE APEX → SQL WORKSHOP → SQL COMMANDS)
----------------------------------------------------------------------------------------------------------------

-- PARENTS TABLE
CREATE TABLE PARENTS (
    PARENT_ID      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME           VARCHAR2(100) NOT NULL,
    AGE            NUMBER CHECK (AGE BETWEEN 18 AND 80),
    GENDER         VARCHAR2(10) CHECK (GENDER IN ('Male','Female','Other')),
    MOBILE         VARCHAR2(15) NOT NULL,
    NUM_CHILDREN   NUMBER CHECK (NUM_CHILDREN >= 0),
    CREATED_AT     DATE DEFAULT SYSDATE
);

-- CHILDREN TABLE
CREATE TABLE CHILDREN (
    CHILD_ID       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PARENT_ID      NUMBER REFERENCES PARENTS(PARENT_ID),
    NAME           VARCHAR2(100) NOT NULL,
    AGE            NUMBER CHECK (AGE BETWEEN 1 AND 12),
    FAV_CATEGORY   VARCHAR2(50),
    CREATED_AT     DATE DEFAULT SYSDATE
);

-- MATERIAL TABLE
CREATE TABLE MATERIALS (
    MATERIAL_ID    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME           VARCHAR2(100),
    TEXTURE        VARCHAR2(30),
    COLOR          VARCHAR2(30),
    SOURCE         VARCHAR2(50),
    SAFETY_CERT    VARCHAR2(50),
    CREATED_AT     DATE DEFAULT SYSDATE
);

----------------------------------------------------------------------------------------------------------------
2. CONSTRAINTS (ADD AFTER TABLE CREATION)
----------------------------------------------------------------------------------------------------------------

ALTER TABLE PARENTS ADD CONSTRAINT CHK_PARENT_MOBILE
CHECK (REGEXP_LIKE(MOBILE, '^[0-9]{10}$'));

ALTER TABLE CHILDREN ADD CONSTRAINT CHK_CHILD_NAME
CHECK (REGEXP_LIKE(NAME, '^[A-Za-z ]+$'));

ALTER TABLE MATERIALS ADD CONSTRAINT CHK_MAT_SOURCE
CHECK (SOURCE IN ('Organic','Recycled','Fabric','Wood'));

----------------------------------------------------------------------------------------------------------------
3. INSERT SAMPLE DATA (MANDATORY FOR REPORTS & CHARTS)
----------------------------------------------------------------------------------------------------------------

INSERT INTO PARENTS (NAME, AGE, GENDER, MOBILE, NUM_CHILDREN)
VALUES ('Meera Patel', 30, 'Female', '9876543210', 1);

INSERT INTO CHILDREN (PARENT_ID, NAME, AGE, FAV_CATEGORY)
VALUES (1, 'Aarav', 3, 'Animals');

INSERT INTO MATERIALS (NAME, TEXTURE, COLOR, SOURCE, SAFETY_CERT)
VALUES ('Eco Wood', 'Smooth', 'Brown', 'Organic', 'ISO-ChildSafe');

COMMIT;

----------------------------------------------------------------------------------------------------------------
4. TRIGGER (MANDATORY PART OF EXAM)
----------------------------------------------------------------------------------------------------------------

-- Audit table
CREATE TABLE CHILD_AUDIT_LOG (
  CHILD_ID NUMBER,
  CHILD_NAME VARCHAR2(100),
  LOG_DATE DATE
);

-- Trigger
CREATE OR REPLACE TRIGGER TRG_CHILD_INSERT
AFTER INSERT ON CHILDREN
FOR EACH ROW
BEGIN
  INSERT INTO CHILD_AUDIT_LOG VALUES (
    :NEW.CHILD_ID,
    :NEW.NAME,
    SYSDATE
  );
END;
/

----------------------------------------------------------------------------------------------------------------
5. WINDOW FUNCTIONS (USE IN APEX → REPORT PAGE → SQL QUERY)
----------------------------------------------------------------------------------------------------------------

-- Rank parents by number of children
SELECT 
    NAME,
    NUM_CHILDREN,
    RANK() OVER (ORDER BY NUM_CHILDREN DESC) AS FAMILY_RANK
FROM PARENTS;

-- Running total
SELECT 
    NAME,
    NUM_CHILDREN,
    SUM(NUM_CHILDREN) OVER (ORDER BY PARENT_ID) AS RUNNING_TOTAL
FROM PARENTS;

----------------------------------------------------------------------------------------------------------------
6. MODEL CLAUSE (APEX REPORT PAGE → SQL QUERY)
----------------------------------------------------------------------------------------------------------------

CREATE TABLE MATERIAL_USAGE (
    MATERIAL_ID NUMBER,
    YEAR_NO NUMBER,
    USAGE_QTY NUMBER
);

-- Forecast future years
SELECT MATERIAL_ID, YEAR_NO, USAGE_QTY
FROM MATERIAL_USAGE
MODEL
    DIMENSION BY (MATERIAL_ID, YEAR_NO)
    MEASURES (USAGE_QTY)
    RULES (
        USAGE_QTY[ANY, FOR YEAR_NO FROM 2025 TO 2028 INCREMENT 1] =
        USAGE_QTY[CV(), CV()-1] * 1.10
    );

----------------------------------------------------------------------------------------------------------------
7. REGEX VALIDATIONS (APPLY IN APEX → PAGE DESIGNER → ITEM → VALIDATION)
----------------------------------------------------------------------------------------------------------------

Name (only alphabets):         ^[A-Za-z ]+$
Mobile number (10 digits):     ^[0-9]{10}$
Date (DD-MM-YYYY):             ^(0[1-9]|[12][0-9]|3[01])-(0[1-9]|1[0-2])-[0-9]{4}$
Email:                         ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

----------------------------------------------------------------------------------------------------------------
8. 10-PAGE APEX APPLICATION – STEP BY STEP CREATION
----------------------------------------------------------------------------------------------------------------

PAGE 1: Dashboard (Home Page)
-----------------------------
Create Page → Blank Page → Add Counts:
SQL Examples:
SELECT COUNT(*) FROM PARENTS;  
SELECT COUNT(*) FROM CHILDREN;  
SELECT COUNT(*) FROM MATERIALS;

PAGE 2: Parent Form
-------------------
Create Page → Form → Form on Table → Select PARENTS
Add Regex Validation:
Item → MOBILE → Validation → Regular Expression → ^[0-9]{10}$

PAGE 3: Parent Report
---------------------
Create Page → Interactive Report
SQL:
SELECT * FROM PARENTS;

PAGE 4: Children Form
---------------------
Create Page → Form on Table → CHILDREN
Create LOV for PARENT_ID:
SELECT NAME, PARENT_ID FROM PARENTS;

PAGE 5: Children Report
-----------------------
Interactive Report → SQL:
SELECT C.*, P.NAME AS PARENT_NAME
FROM CHILDREN C
JOIN PARENTS P ON C.PARENT_ID = P.PARENT_ID;

PAGE 6: Material Form
---------------------
Form on Table → MATERIALS

PAGE 7: Material Report
-----------------------
SELECT * FROM MATERIALS;

PAGE 8: CHART 1 – Children Per Parent (Bar Chart)
-------------------------------------------------
SELECT P.NAME, COUNT(C.CHILD_ID) AS CHILD_COUNT
FROM PARENTS P
LEFT JOIN CHILDREN C ON P.PARENT_ID = C.PARENT_ID
GROUP BY P.NAME;

PAGE 9: CHART 2 – Material Source (Pie Chart)
---------------------------------------------
SELECT SOURCE, COUNT(*)
FROM MATERIALS
GROUP BY SOURCE;

PAGE 10: Window / Model Clause Analytics
----------------------------------------
Choose Report Page → Add window function/model clause SQL from Sections 5 or 6.

----------------------------------------------------------------------------------------------------------------
9. AUTHENTICATION (APPLY IN APEX)
----------------------------------------------------------------------------------------------------------------
Shared Components → Authentication Schemes → Create → From Gallery  
Choose: Application Express Accounts

----------------------------------------------------------------------------------------------------------------
10. AUTHORIZATION (APPLY IN APEX)
----------------------------------------------------------------------------------------------------------------
Shared Components → Authorization Schemes → Create

Admin Only Rule:
RETURN UPPER(:APP_USER) = 'ADMIN';

Apply to page:
Page → Security → Authorization Scheme → Admin Only

----------------------------------------------------------------------------------------------------------------
11. HOW TO MODIFY THIS TEMPLATE FOR ANY EXAM QUESTION
----------------------------------------------------------------------------------------------------------------

Replace:
PARENTS → Students / Customers / Patients  
CHILDREN → Courses / Orders / Visits  
MATERIALS → Products / Medicines / Books  

All SQL, triggers, validations, window functions, model clause, and page structure remain SAME.

----------------------------------------------------------------------------------------------------------------
END OF MASTER TEMPLATE FILE
----------------------------------------------------------------------------------------------------------------
